

with indexes as (
select db_name() as db, user_name(o.schema_id) as [schema], o.object_id, o.type_desc as object_type, o.name as table_name, i.name as index_name
, i.index_id, p.partition_id, p.rows, left(i.type_desc,19) type_desc
, i.data_space_id as file_group_id
, sfg.name as file_group_name
, sf.name as logical_file_name
, sf.filename as physical_file_name
, case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,1) is null then NULL else ( select (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,1 )) + case when indexkey_property(i.object_id, i.index_id, 1, 'isdescending') = 1 then '(-)' else '' end
+ case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,2) is not null then ', ' + (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,2)) + case when indexkey_property(i.object_id, i.index_id, 2, 'isdescending') = 1 then '(-)' else '' end else '' end
+ case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,3) is not null then ', ' + (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,3)) + case when indexkey_property(i.object_id, i.index_id, 3, 'isdescending') = 1 then '(-)' else '' end else '' end
+ case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,4) is not null then ', ' + (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,4)) + case when indexkey_property(i.object_id, i.index_id, 4, 'isdescending') = 1 then '(-)' else '' end else '' end
+ case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,5) is not null then ', ' + (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,5)) + case when indexkey_property(i.object_id, i.index_id, 5, 'isdescending') = 1 then '(-)' else '' end else '' end
+ case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,6) is not null then ', ' + (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,6)) + case when indexkey_property(i.object_id, i.index_id, 6, 'isdescending') = 1 then '(-)' else '' end else '' end
+ case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,7) is not null then ', ' + (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,7)) + case when indexkey_property(i.object_id, i.index_id, 7, 'isdescending') = 1 then '(-)' else '' end else '' end
+ case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,8) is not null then ', ' + (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,8)) + case when indexkey_property(i.object_id, i.index_id, 8, 'isdescending') = 1 then '(-)' else '' end else '' end
+ case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,9) is not null then ', ' + (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,9)) + case when indexkey_property(i.object_id, i.index_id, 9, 'isdescending') = 1 then '(-)' else '' end else '' end
+ case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,10) is not null then ', ' + (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,10)) + case when indexkey_property(i.object_id, i.index_id, 10, 'isdescending') = 1 then '(-)' else '' end else '' end
+ case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,11) is not null then ', ' + (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,11)) + case when indexkey_property(i.object_id, i.index_id, 11, 'isdescending') = 1 then '(-)' else '' end else '' end
+ case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,12) is not null then ', ' + (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,12)) + case when indexkey_property(i.object_id, i.index_id, 12, 'isdescending') = 1 then '(-)' else '' end else '' end
+ case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,13) is not null then ', ' + (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,13)) + case when indexkey_property(i.object_id, i.index_id, 13, 'isdescending') = 1 then '(-)' else '' end else '' end
+ case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,14) is not null then ', ' + (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,14)) + case when indexkey_property(i.object_id, i.index_id, 14, 'isdescending') = 1 then '(-)' else '' end else '' end
+ case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,15) is not null then ', ' + (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,15)) + case when indexkey_property(i.object_id, i.index_id, 15, 'isdescending') = 1 then '(-)' else '' end else '' end
+ case when INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,16) is not null then ', ' + (INDEX_COL(user_name(o.schema_id)+'.'+o.name,i.index_id,16)) + case when indexkey_property(i.object_id, i.index_id, 16, 'isdescending') = 1 then '(-)' else '' end else '' end
) end as [columns]
, i.is_primary_key, i.is_unique_constraint, i.is_disabled, i.allow_row_locks, i.allow_page_locks
, convert(varchar,stats_date(i.object_id,i.index_id),120) as [last update stats]
, indexproperty(i.object_id,i.name,'IndexFillFactor') as [fillFactor]
, indexproperty(i.object_id,i.name,'IndexDepth') as [depth]
, indexproperty(i.object_id,i.name,'IsPadindex') as [padindex]
, i.ignore_dup_key, i.is_unique, i.is_hypothetical, s.auto_created, s.no_recompute
from sys.all_objects o 
join sys.indexes i on i.object_id = o.object_id and i.index_id < 255
left outer join sys.stats s on i.object_id = s.object_id and i.index_id = s.stats_id
join sys.partitions p on p.object_id = i.object_id and p.index_id = i.index_id 
join sys.allocation_units a on  p.partition_id = a.container_id
inner join sys.filegroups sfg on sfg.data_space_id = i.data_space_id
inner join sys.sysfiles sf on sf.groupid = sfg.data_space_id 
where objectproperty(i.object_id,'ismsshipped') =0 )
select quotename(db_name())+'.'+quotename([schema])+'.'+quotename(indexes.table_name) 
, indexes.index_name, indexes.index_id, indexes.type_desc	--, indexes.object_type
, indexes.rows
, [last update stats]
, a.type_desc
, indexes.[columns]
, sum(a.total_pages) as total_pages, sum(a.used_pages) as used_pages, sum(a.data_pages) as data_pages
, (sum(a.total_pages) * 8) / 1024 as total_space_mb, (sum(a.used_pages) * 8) / 1024 as used_space_mb, (sum(a.data_pages) * 8) / 1024 as data_space_mb
, file_group_name, logical_file_name, physical_file_name
from indexes
join sys.allocation_units a on indexes.partition_id = a.container_id
group by indexes.[schema],indexes.table_name, indexes.object_id, indexes.object_type, indexes.partition_id, indexes.index_name, indexes.index_id, indexes.rows, indexes.type_desc, a.type_desc
, file_group_name, logical_file_name, physical_file_name, [last update stats]
, indexes.[columns]
order by 1, 2, 3
